package com.nirvana.utils;

import com.intellij.openapi.ui.Messages;
import com.nirvana.config.PluginConfig;
import com.nirvana.data.DbFieldInfo;
import org.jetbrains.annotations.Nullable;

import java.sql.*;
import java.util.*;

public class DbUtil
{
    public static Connection getConn() throws ClassNotFoundException,
            SQLException
    {
        PluginConfig pluginConfig = PluginConfig.getInstance();
        Class.forName(pluginConfig.getDriver());
        return DriverManager.getConnection(PluginConfig.mainPanel.ormDbUrlTextField.getText(), pluginConfig.user(), pluginConfig.password());
    }

    public static Connection getConn(String dbName)
            throws ClassNotFoundException, SQLException
    {
        PluginConfig pluginConfig = PluginConfig.getInstance();
        Class.forName(pluginConfig.getDriver());
        return DriverManager.getConnection(pluginConfig.dbUrl() + "/" + dbName, pluginConfig.user(), pluginConfig.password());
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn)
    {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (Exception e) {
            showErrorDialog(e);
        }
    }

    /**
     * 获得当前数据库所有表名
     */
    public static List<String> getTables()
    {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<String> tables = new ArrayList<>();
        try
        {
            conn = DbUtil.getConn();
            stmt = conn.prepareStatement("SELECT table_name FROM information_schema.tables WHERE table_schema = '%s'".formatted(PluginConfig.getInstance().dbName()));
            rs = stmt.executeQuery();

            while (rs.next()) {
                String tableName = rs.getString("table_name");
                tables.add(tableName);
            }
        }
        catch (Exception e)
        {
            showErrorDialog(e);
        }
        finally
        {
            DbUtil.close(rs, stmt, conn);
        }
        return tables;
    }

    private static void showErrorDialog(Exception e) {
        Messages.showErrorDialog(e.getMessage(), "数据库连接错误");
        throw new RuntimeException(e);
    }

    private static LinkedHashMap<String, DbFieldInfo> computeColumnInfo(String dbName, String tableName) {
        LinkedHashMap<String, DbFieldInfo> fieldMap = new LinkedHashMap<>();
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            // 获得数据表各个字段的注释
            conn = DbUtil.getConn("information_schema");
            String sql = "SELECT * FROM COLUMNS WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'".formatted(dbName, tableName);
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();

            while (rs.next())
            {
                DbFieldInfo field = new DbFieldInfo();
                String columnName = rs.getString("column_name");
                field.setColumnName(columnName);
                field.setComment(rs.getString("column_comment"));
                field.setFlag(true);
                fieldMap.put(columnName, field);
            }
        } catch (Exception e) {
            showErrorDialog(e);
        }
        finally {
            close(rs,pstm, conn);
        }
        return fieldMap;
    }

    /**
     * 获得数据表具体信息
     */
    public static Collection<DbFieldInfo> getTableFieldList(String tableName)
    {
        String dbName = PluginConfig.getInstance().dbName();
        LinkedHashMap<String, DbFieldInfo> fieldMap = computeColumnInfo(dbName, tableName);
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;

        try
        {
            conn = DbUtil.getConn();
            String sql = "select * from `%s`".formatted(tableName);
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            ResultSetMetaData rsMetaData = rs.getMetaData();

            int cols = rsMetaData.getColumnCount();
            for (int i = 1; i <= cols; i++)
            {
                String columnName = rsMetaData.getColumnName(i);
                DbFieldInfo dbFieldInfo = fieldMap.get(columnName);
                if (dbFieldInfo != null)
                {
                    String javaType = computeJavaType(rsMetaData.getColumnClassName(i));
                    String sqlType = computeSqlType(rsMetaData.getColumnTypeName(i));
                    dbFieldInfo.setJavaType(javaType);
                    dbFieldInfo.setSqlType(sqlType);
                    dbFieldInfo.setLen((rsMetaData.getColumnDisplaySize(i)));
                }
            }

            // 主键信息
            ResultSet primaryKeys = conn.getMetaData().getPrimaryKeys(null, dbName, tableName);
            while (primaryKeys.next())
            {
                String columnName = primaryKeys.getObject(4).toString();
                DbFieldInfo dbFieldInfo = fieldMap.get(columnName);
                if (dbFieldInfo != null) {
                    dbFieldInfo.setPrimaryKey(true);
                    ResultSet clmSet = conn.getMetaData().getColumns(null, dbName, tableName, columnName);
                    while (clmSet.next())
                    {
                        String flag = (String) clmSet.getObject("IS_AUTOINCREMENT");
                        if (flag.equals("YES"))
                        {
                            dbFieldInfo.setAutoIncrement(true);
                        }

                    }
                }
            }
        }
        catch (Exception e)
        {
            showErrorDialog(e);
        }
        finally
        {
            DbUtil.close(rs, pstm, conn);
        }
        return fieldMap.values();
    }

    private static String computeSqlType(String sqlType) {
        if (sqlType.contains("UNSIGNED"))
        {
            sqlType = sqlType.replace("UNSIGNED", "").trim();
        }
        return sqlType;
    }

    @Nullable
    private static String computeJavaType(String javaType) {
        if (Objects.equals(javaType, "[B"))
        {
            javaType = "byte[]";
        }
        else if (Objects.equals(javaType, "java.lang.Boolean"))
        {
            javaType = "boolean";
        }
        else if (Objects.equals(javaType, "java.lang.Integer"))
        {
            javaType = "int";
        }
        else if (Objects.equals(javaType, "java.lang.Long"))
        {
            javaType = "long";
        }
        else if (Objects.equals(javaType, "java.lang.Float"))
        {
            javaType = "float";
        }
        else if (Objects.equals(javaType, "java.lang.String"))
        {
            javaType = "String";
        }
        else if (Objects.equals(javaType, "java.sql.Date"))
        {
            javaType = "Date";
        }
        else if (Objects.equals(javaType, "java.time.LocalDateTime"))
        {
            javaType = "Date";
        }
        else if (Objects.equals(javaType, "java.sql.Timestamp"))
        {
            javaType = "Date";
        }
        else if (Objects.equals(javaType, "java.math.BigInteger"))
        {
            javaType = "long";
        }
        return javaType;
    }

}
